﻿using System;
using System.Collections.Generic;
using System.Data;
using SAS.Core.Entity;
using SAS.Core.Result;

namespace SAS.DataAccess
{
    public class UserDAO : BaseDAO
    {
        #region Contructors
        public UserDAO()
            : base(string.Empty)
        {

        }
        public UserDAO(string tokenKey)
            : base(tokenKey)
        {

        }
        #endregion
        
        /// <summary>
        /// Login to system by user email, hashed password and MD5 secret key. Return token key if login successfully.
        /// </summary>
        /// <param name="email">User email.</param>
        /// <param name="hashedPassword"></param>
        /// <param name="secret">MD5 secret code</param>
        /// <returns></returns>
        public SASResult<string> Login(string email, string hashedPassword, string secret)
        {
            //Indentify SQL store procedure name
            const string storeProcedureName = "[scmAdminApp].[uspUser_LogIn]";

            //Build SQL parameters and their values.
            Parameter[] sqlParameters = new[]
                                            {
                                                new Parameter
                                                    {
                                                        Name = "@Token",
                                                        Value = "",
                                                        ParameterDirection = ParameterDirection.Output,
                                                        Size = 36,
                                                        OutputReturnName = "TokenKey"
                                                    },
                                                new Parameter {Name = "@Email", Value = email},
                                                new Parameter {Name = "@HashedPW", Value = hashedPassword},
                                                new Parameter {Name = "@Secret", Value = secret},
                                                new Parameter
                                                    {
                                                        Name = "@DisplayName",
                                                        Value = "",
                                                        ParameterDirection = ParameterDirection.Output,
                                                        Size = 50,
                                                        OutputReturnName = "DisplayName"
                                                    }
                                            };

            //Execute SQL and get object returned by sql query.
            SASResult<String> dataAccessResult = DB.ExecuteScalar<String>(storeProcedureName, sqlParameters);

            //If user login successfully --> update current data access token key
            if (dataAccessResult.IsSucessful &&
                dataAccessResult.OutputResult.ContainsKey("TokenKey"))
            {
                TokenKey = dataAccessResult.OutputResult["TokenKey"];
            }

            //return
            return dataAccessResult;
        }

        /// <summary>
        /// Logout
        /// </summary>
        /// <returns></returns>
        public SASResult<User> LogOut()
        {
            //Indentify SQL store procedure name
            const string storeProcedureName = "[scmAdminApp].[uspUser_LogOut]";

            //Build SQL parameters and their values.
            Parameter[] sqlParameters = new[]
                                            {
                                                new Parameter {Name = "@Token", Value = TokenKey}
                                            };

            return DB.ExecuteScalar<User>(storeProcedureName, sqlParameters);
        }

        /// <summary>
        /// Get user role name
        /// </summary>
        /// <returns></returns>
        public SASResult<IList<string>> GetUserRoles()
        {
            //identify user role column name
            const string columnName = "UserRole";

             //Indentify structure that parse sql value to object.
            Func<IDataReader, string> make = reader => Convert.ToString(reader["UserRoleName"]);

            //Call to common function to get list of item
            return GetDropDownList(columnName, make);
        }

        /// <summary>
        /// Get user by token and paging
        /// </summary>
        /// <param name="pagenumber">page number</param>
        /// <param name="pagesize">page size</param>
        /// <param name="orderby">order by column</param>
        /// <param name="conditionList">conditionList</param>
        /// <returns></returns>
        public SASResult<IList<User>> GetUsers(int pagenumber, int pagesize, string orderby, string conditionList)
        {
           
            //Indentify view name
            const string viewName = "[scmAdminApp].[vwUserSummary]";

            //Indentify structure that parse sql results to objects.
            Func<IDataReader, User> make = reader => new User
                                                         {
                                                             Id = Convert.ToInt32(reader["pkUser_ID"]),
                                                             FirstName = Convert.ToString(reader["Firstname"]),
                                                             LastName = Convert.ToString(reader["Lastname"]),
                                                             DisplayName = Convert.ToString(reader["DisplayName"]),
                                                             Email = Convert.ToString(reader["Email"]),
                                                             Title = Convert.ToString(reader["Title"]),
                                                             Phone1 = Convert.ToString(reader["Phone1"]),
                                                             Phone2 = Convert.ToString(reader["Phone2"]),
                                                             Address1 = Convert.ToString(reader["Address1"]),
                                                             Address2 = Convert.ToString(reader["Address2"]),
                                                             City = Convert.ToString(reader["City"]),
                                                             StateProv = Convert.ToString(reader["StateProv"]),
                                                             PostalCode = Convert.ToString(reader["PostalCode"]),
                                                             Country = Convert.ToString(reader["Country"]),
                                                             ClientRoleName = Convert.ToString(reader["UserRoleName"]),
                                                             CreatedBy = Convert.ToString(reader["CreatedBy"]),
                                                             CreatedOn = Convert.ToDateTime(reader["CreatedDate"]).ToShortDateString()
                                                         };

            //call to common method Search
            return Search(pagenumber, pagesize, orderby, conditionList, viewName, make);
        } 

        /// <summary>
        /// 
        /// </summary>
        /// <param name="callFrom"></param>
        /// <returns></returns>
        public SASResult<string> ValidateToken(string callFrom)
        {
            //Indentify SQL store procedure name
            const string storeProcedureName = "[scmAdminApp].[uspUser_ValidateToken]";

            //Build SQL parameters and their values.
            Parameter[] sqlParameters = new[]
                                            {
                                                new Parameter {Name = "@Token", Value = TokenKey},
                                                new Parameter {Name = "@CalledFrom", Value = callFrom},
                                                new Parameter
                                                    {
                                                        Name = "@pkUser_ID",
                                                        Value = string.Empty,
                                                        DbType = SqlDbType.Int,
                                                        OutputReturnName = "PKUserID",
                                                        ParameterDirection = ParameterDirection.Output
                                                    },
                                                new Parameter
                                                    {
                                                        Name = "@DisplayName",
                                                        Value = string.Empty,
                                                        OutputReturnName = "DisplayName",
                                                        ParameterDirection = ParameterDirection.Output
                                                    },
                                                new Parameter
                                                    {
                                                        Name = "@UserStatus",
                                                        Value = string.Empty,
                                                        OutputReturnName = "UserStatus",
                                                        ParameterDirection = ParameterDirection.Output
                                                    }
                                            };

            return DB.ExecuteScalar<string>(storeProcedureName, sqlParameters);
        } 

        /// <summary>
        /// Create new user.
        /// </summary>
        /// <param name="user">user information</param>
        /// <returns></returns>
        public SASResult<User> Set(User user)
        {
            //Indentify SQL store procedure name
            const string storeProcedureName = "[scmAdminApp].[uspUserRec_SetByToken$_ID]";

            //Build SQL parameters and their values.
            Parameter[] sqlParameters = new[]
                                            {
                                                new Parameter{Name = "@Token", Value = TokenKey},
                                                new Parameter
                                                            {
                                                                Name = "@pkUser_ID",
                                                                Value = user.Id,
                                                                DbType = SqlDbType.Int,
                                                                OutputReturnName = "UserId",
                                                                ParameterDirection = ParameterDirection.Output
                                                            },
                                                new Parameter{Name = "@Firstname", Value = user.FirstName},
                                                new Parameter{Name = "@Lastname", Value = user.LastName},
                                                new Parameter{Name = "@Email", Value = user.Email},
                                                new Parameter{Name = "@Title", Value = user.Title},
                                                new Parameter{Name = "@Phone1", Value = user.Phone1},
                                                new Parameter{Name = "@Phone2", Value = user.Phone2},
                                                new Parameter{Name = "@Address1", Value = user.Address1},
                                                new Parameter{Name = "@Address2", Value = user.Address2},
                                                new Parameter{Name = "@City", Value = user.City},
                                                new Parameter{Name = "@StateProv", Value = user.StateProv},
                                                new Parameter{Name = "@PostalCode", Value = user.PostalCode},
                                                new Parameter{Name = "@Country", Value = user.Country},
                                                new Parameter{Name = "@IsActive", Value = user.IsActive, DbType = SqlDbType.Bit},
                                                new Parameter{Name = "@UserRoleName", Value = user.ClientRoleName}
                                            };

            return DB.ExecuteScalar<User>(storeProcedureName, sqlParameters);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="email"></param>
        /// <returns></returns>
        public SASResult<string> ResetPassword(string email)
        {
            //Indentify SQL store procedure name
            const string storeProcedureName = "[scmAdminApp].[uspUserRec_PasswordReset]";

            //Build SQL parameters and their values.
            Parameter[] sqlParameters = new[]
                                            {
                                                new Parameter {Name = "@Email", Value = email},
                                                new Parameter
                                                    {
                                                        Name = "@IsValidEmail",
                                                        OutputReturnName = "IsValidEmail",
                                                        ParameterDirection = ParameterDirection.Output,
                                                        DbType = SqlDbType.TinyInt
                                                    },
                                                new Parameter
                                                    {
                                                        Name = "@FirstName",
                                                        OutputReturnName = "FirstName",
                                                        ParameterDirection = ParameterDirection.Output,
                                                        DbType = SqlDbType.VarChar,
                                                        Size = 20
                                                    },
                                                new Parameter
                                                    {
                                                        Name = "@NewPassword",
                                                        OutputReturnName = "NewPassword",
                                                        ParameterDirection = ParameterDirection.Output,
                                                        DbType = SqlDbType.VarChar,
                                                        Size = 50
                                                    }
                                            };

            return DB.ExecuteScalar<string>(storeProcedureName, sqlParameters);
        }

        public SASResult<string> UpdatePassword(string hashedPW, string secret, string newPassword)
        {
            //Indentify SQL store procedure name
            const string storeProcedureName = "[scmAdminApp].[uspUserRec_ChangePasswordByToken]";

            //Build SQL parameters and their values.
            Parameter[] sqlParameters = new[]
                                            {
                                                new Parameter {Name = "@Token", Value = TokenKey},
                                                new Parameter
                                                    {
                                                        Name = "@HashedPW",
                                                        OutputReturnName = "HashedPW",
                                                        ParameterDirection = ParameterDirection.Output,
                                                        Value = hashedPW
                                                    },
                                                new Parameter
                                                    {
                                                        Name = "@Secret",
                                                        OutputReturnName = "Secret",
                                                        ParameterDirection = ParameterDirection.Output,
                                                        Value = secret
                                                    },
                                                new Parameter
                                                    {
                                                        Name = "@NewPassword",
                                                        OutputReturnName = "NewPassword",
                                                        ParameterDirection = ParameterDirection.Output,
                                                        Value = newPassword
                                                    }
                                            };

            return DB.ExecuteScalar<string>(storeProcedureName, sqlParameters);
        }

    }
}